PostgreSQL: The Swiss Army Knife You're Not Using

Devoxx France
2025-04-18

Who am I

• Lætitia Avrot
• PostgreSQL recognized contributor
• PostgreSQL Europe board member
• #PostgresWomen co-founder
• EDB Practice Leader
• University teacher in Lyon
• mydbanotebook.org / psql-tips.org

PostgreSQL: The Swiss Army Knife You're Not Using

• Range
• Generated columns
• Full text search
• LISTEN/NOTIFY
• RETURNING

Storing renting data

• customer: Contains customer details.
• bike: Holds information about the bikes.
• rental: Matches customers with the bikes they rented.

Storing renting data

Storing renting data

Storing renting data

Storing renting data

Storing renting data

Storing renting data

Storing renting data

Storing renting data

Storing renting data

Storing renting data

Storing renting data

The Range datatype

• Represents a range of values
• Supports multiple types
• Inclusive/exclusive boundaries
• Supports multirange

The Range datatype

Examples
• '(10,50)'::int4range
• '[15.99, 29.99]'::numrange
• '[2025-04-01,2025-04-05)'::daterange
• '{[1,5), [10,15)}'::int4range[];

Operators

• +, *, -
• @>
• &&
• >>
• -|-

Indexing range

Using range

create table Rental (
  Bike_Id integer,
  Customer_Id integer,
  Rental_Range tstzrange,
  /* Constraints */
  primary key (Bike_Id, Customer_Id),
  exclude using gist (Bike_Id with =, Rental_Range with &&)
);

Generating ids

• Sequences
• Default as sequence.next()
• Serial datatypes
• Identity columns

Sequences

laetitia=# create table test(
  id integer primary key,
  value text);
CREATE TABLE
laetitia=# create sequence my_seq;
CREATE SEQUENCE
laetitia=# insert into test (
  select nextval('my_seq'),
    'blabla');
INSERT 0 1

Automatic sequences

laetitia=# create sequence my_seq;
CREATE SEQUENCE
laetitia=# create table test (
  id integer default nextval('my_seq') primary key,
  value text);
CREATE TABLE
laetitia=# insert into test(value) values ('blabla');
INSERT 0 1

The serial datatype

laetitia=# create table test (
  id serial primary key,
  value text);
CREATE TABLE
laetitia=# insert into test (value) values ('blabla');
INSERT 0 1

The identity column

laetitia=# create table test (
  id integer generated always as identity primary key,
  value text);
CREATE TABLE
laetitia=# insert into test (value) values ('blabla');
INSERT 0 1

The identity column

Prevents from inserting or updating manually this column

laetitia=# insert into test (id, value) values (2,'blabla');

ERROR:  cannot insert a non-DEFAULT value into column "id"

DETAIL:  Column "id" is an identity column defined
as GENERATED ALWAYS.

HINT:  Use OVERRIDING SYSTEM VALUE to override.

Generating ids

Duration

How much time was that bike rented to that customer?

select
  ceil(
    extract(
      epoch from (
        upper(Rental_Range) - lower(Rental_Range)
      )
    ) / (12 * 3600)
  )
  from Rental
  where Bike_Id = X
    and Customer_Id = Y;

Duration

How much time was that bike rented to that customer?

Duration

create table Rental (
  Bike_Id integer,
  Customer_Id integer,
  Rental_Range tstzrange,
  /* Generated column */
  Rental_duration integer always generated as (
    ceil(extract(epoch from (upper(Rent_Range) - lower(Rent_Range))) / (12 * 3600)) * 0.5
  ) stored,
  /* Constraints */
  primary key (Bike_Id, Customer_Id),
  exclude using gist (Bike_Id with =, Rental_Range with &&)
);

Duration

select Rental_Duration
  from Rental
  where Bike_Id = X
    and Customer_Id = Y;

Price

select price(Rental_Duration)
  from Rental
  where Bike_Id = X
    and Customer_Id = Y;

Price

Price

create table Rental (
  Bike_Id integer,
  Customer_Id integer,
  Rental_Range tstzrange,
  /* Generated column */
  Rental_duration integer always generated as (
    ceil( extract(epoch from upper(Rent_Range)) - extract(epoch from lower(Rent_Range))) / (12 * 3600)) * 0.5
  ) stored,
  /* Columns with trigger */
  Price numeric(6,2),
  /* Constraints */
  primary key (Bike_Id, Customer_Id),
  exclude using gist (Bike_Id with =, Rental_Range with &&)
);

Price

create or replace function set_price()
returns triggeR
language plpgsql
as
$set_price$
begin
   if new.price is null then
     new.price=price(new.Rental_Range);
  end if;
  return new;
end;
$set_price$;

Price

create trigger Rental_before_insert
before insert
on Rental
for each row
execute function set_price();

Full Text search

Regular expressions

create table bike (
  bike_Id integer generated always by identity,
  /* Enum columns */
  category bike_category,
  size bike_size,
  /* other search criteria*/
  description text,
  primary key (Bike_Id)
)
  

create index Bike_Description on
  Bike (description text_pattern_ops);

Regular expressions

select Bike_Id, description
from bike
where description ~* '21[- ]speed|full suspension';

Full-text Search

create table bike (
  bike_Id integer generated always by identity,
  /* Enum columns */
  category bike_category,
  size bike_size,
  /* other search criteria*/
  description tsvector,
  primary key (Bike_Id)
)
  

create index Bike_description_tsv on
  bikes using gin (description);

Full-text Search

select Bike_Id, description
from bike
where description_tsv @@
  to_tsquery('english', 'electric & "21-speed"');

NOTIFY

NOTIFY

create or replace function notify_repair_needed()
returns trigger as
$notify_repair_needed$
begin
  if new.needs_repair and not old.needs_repair then
    notify repairs, new.Bike_Id;
  end if;
  return new;
end;
$notify_repair_needed$ language plpgsql;

LISTEN

def listen_for_notifications(self):
  self.cursor.execute("listen repairs;")
  print("Listening for repair notifications...")

  while True:
    # Check for notifications
    self.conn.poll()

    while self.conn.notifies:
      notify = self.conn.notifies.pop()
      bike_id = notify.payload
      print(f"Received repair notification for bike {bike_id}")
      self.bikes_needing_repair.add(bike_id)

RETURNING

insert into rental (
  Bike_Id,
  customer_Id,
  Rent_Range)

  select 1, 1, tstzrange(now(), 'infinity')

  returning price;
);
          

RETURNING

with myBike (id) as (insert into Bike (
  Category,
  Size,
  Description)

  select 'road',
    'M',
    $$The new Carbon All-Road v2 is built for bigger adventures
    ...
    bike for the latest drivetrain tech and easier maintenance.$$

  returning Bike_Id
)

insert into Rental (
  Bike_Id,
  Customer_Id,
  Rent_Range)

  select id, 1, tstzrange(now(), 'infinity')
  from myBike

  returning price
;
          

One tool to rule them all

• Postgres is versatile
• No more complex stacks
• Make the devs happy!

Scannez le QR code et tentez de gagner un livre sur PostgreSQL avec EDB !